﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace EasyInvoice.SqlCEDAL
{
    public class Users:IUsers
    {
        public EasyInvoice.Model.User GetById(long id)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    var p = new DynamicParameters();

                    string sql = "SELECT * FROM [Users] WHERE [Id] = @Id";
                    p.Add("@Id", id, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    IEnumerable<EasyInvoice.Model.User> users = sqlConnection.Query<EasyInvoice.Model.User>(sql, p, commandType: CommandType.Text);
                    sqlConnection.Close();
                    if (users == null)
                    {
                        return null;
                    }
                    else
                    {
                        EasyInvoice.Model.User returnUser = users.FirstOrDefault<EasyInvoice.Model.User>();
                        return returnUser;
                    }
                }
            }
            catch (Exception) 
            { 
                throw; 
            }
        }

        public IEnumerable<EasyInvoice.Model.User> GetAll()
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    string sql = string.Format("SELECT * FROM [Users]");

                    IEnumerable<EasyInvoice.Model.User> users = sqlConnection.Query<EasyInvoice.Model.User>(sql, commandTimeout: 0, commandType: CommandType.Text);

                    sqlConnection.Close();

                    return users;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public long Save(EasyInvoice.Model.User user)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();

                    var parameters = new DynamicParameters();
                    parameters.Add("@Id", user.Id, System.Data.DbType.Int32, direction: ParameterDirection.Input);
                    parameters.Add("@UserName", user.UserName, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@Password", user.Password, System.Data.DbType.String, direction: ParameterDirection.Input);
                    parameters.Add("@Rights", user.Rights, System.Data.DbType.Int64, direction: ParameterDirection.Input);

                    string sql = string.Empty;
                    if (user.Id == 0)
                        sql = string.Format("INSERT INTO [Users] ( [UserName], [Password], [Rights]) VALUES(@UserName, @Password, @Rights);");
                    else
                        sql = string.Format("UPDATE [Users] SET [UserName] = @UserName, [Password] = @Password, [Rights] = @Rights WHERE [Id] = @Id");

                    sqlConnection.Execute(sql, parameters, null, commandTimeout: 0, commandType: CommandType.Text);
                    if (user.Id == 0)
                    {
                        sql = "SELECT * FROM [Users] WHERE [Id] = @@IDENTITY";
                        IEnumerable<EasyInvoice.Model.User> users = sqlConnection.Query<EasyInvoice.Model.User>(sql, commandType: CommandType.Text);
                        user.Id = users.First().Id;
                    }

                    sqlConnection.Close();

                    return user.Id;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public EasyInvoice.Model.User Delete(EasyInvoice.Model.User user)
        {
            try
            {
                using (System.Data.SqlServerCe.SqlCeConnection sqlConnection = new System.Data.SqlServerCe.SqlCeConnection(Properties.Settings.Default.ConnectionString))
                {
                    sqlConnection.Open();
                    var p = new DynamicParameters();
                    p.Add("@Id", user.Id, System.Data.DbType.Int32, direction: ParameterDirection.Input);

                    string sql = "DELETE FROM [Users] WHERE [Id] = @Id";

                    sql = string.Format(sql);
                    sqlConnection.Execute(sql, p, commandType: CommandType.Text);

                    sqlConnection.Close();
                    return user;
                }
                
            }
            catch (Exception)
            {
                throw;
            }
            
        }
    }
}
